package com.renjun.smweb.service.EnrollmentService.impl;

import com.alibaba.fastjson.JSONObject;
import com.renjun.smweb.commons.componet.BaseDaoComponent;
import com.renjun.smweb.commons.componet.FileIOComponent;
import com.renjun.smweb.commons.enums.EOperators;
import com.renjun.smweb.commons.global.DataMap;
import com.renjun.smweb.commons.global.FileInfoPo;
import com.renjun.smweb.commons.util.*;
import com.renjun.smweb.dao.bean.InsertParams;
import com.renjun.smweb.dao.bean.Parameter;
import com.renjun.smweb.dao.bean.QueryParams;
import com.renjun.smweb.dao.bean.UpdateParams;
import com.renjun.smweb.dao.pojo.LoginInfo;
import com.renjun.smweb.exceptions.BaseException;
import com.renjun.smweb.exceptions.DataNotFoundException;
import com.renjun.smweb.exceptions.EnrollOpenTimeException;
import com.renjun.smweb.exceptions.FileTypeNotFoundException;
import com.renjun.smweb.service.EnrollmentService.EnrollmentService;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.time.DateUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.*;

import static com.renjun.smweb.dao.bean.Parameter.createParameter;
import static com.renjun.smweb.dao.bean.Parameter.createParameters;

/**
 * Created by RenJun on 2017/2/21.
 */
@Service
public class EnrollmentServiceImpl implements EnrollmentService {
    @Autowired
    private BaseDaoComponent baseDaoComponent;

    /** 招生计划列表 **/
    @Override
    public JSONObject enrollPlanList(Map<String, Object> params) throws SQLException, DataNotFoundException {
        LoginInfo loginInfo = (LoginInfo) params.remove("userinfo");
        QueryParams queryParams = QueryParams.createQueryParams("V_M51_ENROLLPLANLIST_NK");
        Map<String,Object> map = new DataMap<>();
        for(Map.Entry<String,Object> m:params.entrySet()){
            if(m.getValue()!=null&&!m.getValue().toString().isEmpty())
                map.put(m.getKey(),m.getValue());
        }
        queryParams.addQueryParamsByMap(map);
        queryParams.printSelf();
        try {
            queryParams.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Map<String, Object>> result = baseDaoComponent.selectDataByParams(queryParams);
        JSONObject rs = ResponseUtils.createSuccessResponseBody("done", result);
     //   if(!result.isEmpty()) rs.put("total",result.get(0).get("total"));
        return rs;
    }

    /** 招生计划列表统计 **/
    @Override
    public JSONObject enrollPlanListStat(Map<String, Object> params) throws SQLException, DataNotFoundException{
        LoginInfo loginInfo = (LoginInfo) params.remove("userinfo");
        params.remove("sort");
        params.remove("offset");
        params.remove("limit");
        params.remove("order");
        QueryParams queryParams = QueryParams.createQueryParams("V_M51_ENROLLPLANLIST_NK");
        queryParams.sethasColums(true);
        Map<String,Object> map = new DataMap<>();
        for(Map.Entry<String,Object> m:params.entrySet()){
            if(m.getValue()!=null&&!m.getValue().toString().isEmpty())
                map.put(m.getKey(),m.getValue());
        }
        queryParams.addQueryParamsByMap(map);
        queryParams.addColumns("sum(TBRS) as D1",   //托班人数
                "sum(TBBS) as D2",  //托班班数
                "sum(XBRS) as D3",   //小班人数
                "sum(XBBS) as D4");   //小班班数
        try {
            queryParams.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        queryParams.printSelf();
        List<Map<String, Object>> result = baseDaoComponent.selectDataByParams(queryParams);
        JSONObject rs = ResponseUtils.createSuccessResponseBody("done", result.get(0));
        return rs;
    }

    /** 招生计划列表导出 **/
    @Override
    public JSONObject expEnrollPlanList(Map<String, Object> params) throws SQLException, DataNotFoundException, FileTypeNotFoundException, IOException {
        LoginInfo loginInfo = (LoginInfo) params.remove("userinfo");
        params.remove("sort");
        params.remove("offset");

        List<Parameter> list = Parameter.createParameters(params);
        QueryParams queryParams = QueryParams.createQueryParams("V_M51_ENROLLPLANLIST_NK");
        for (Parameter p : list) {
            if (p.getColumnName().trim().equals("order") || p.getColumnName().trim().equals("limit")) {
                continue;
            }
            if(p.getDataValues().get(0).getValue()==null||p.getDataValues().get(0).getValue().toString().isEmpty())continue;
            queryParams.addQueryParams(p);
        }
        try {
            queryParams.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        List<Map<String, Object>> result = baseDaoComponent.selectDataByParams(queryParams);
        queryParams.printSelf();

        //导出招生计划列表excel
        FileInfoPo fileInfoPo = FileIOComponent.createTempFilePathByType(FileIOComponent.EXPORT_TYPE,"xlsx","招录管理-招生计划管理");
        Map<String, Object> filePath = new HashMap<>();
        filePath.put("url", fileInfoPo.getFileVirPath());
        //写入excel表格
        ExportExcel excelHelper = ExportExcel.createExportExcel(fileInfoPo.getFileAbsPath(),"{SCHOOLNAME:学校,TBRS:托班招生计划,TBBS:托班招生班级,XBRS:小班招生计划,XBBS:小班招生班级,XXBBMC:学校办别,BXDJMC:办学等级}");
        for (int i =0,size = result.size();i<size;i++){
            Map<String,Object> data = result.get(i);
            try {
                result.get(i).put("SCHOOLNAME",data.get("SCHOOLNAME")==null?null:data.get("SCHOOLNAME").toString());
                result.get(i).put("TBRS",data.get("TBRS")==null?null:data.get("TBRS").toString());
                result.get(i).put("TBBS",data.get("TBBS")==null?null:data.get("TBBS").toString());
                result.get(i).put("XBRS",data.get("XBRS")==null?null:data.get("XBRS").toString());
                result.get(i).put("XBBS",data.get("XBBS")==null?null:data.get("XBBS").toString());
                result.get(i).put("XXBBMC",data.get("XXBBMC")==null?null:data.get("XXBBMC").toString());
                result.get(i).put("BXDJMC",data.get("BXDJMC")==null?null:data.get("BXDJMC").toString());
            }catch(Exception e){}
        }
        excelHelper.setCurrentData(result);
        excelHelper.writeCurrentData();
        JSONObject rs = ResponseUtils.createSuccessResponseBody("done", filePath);
        return rs;
    }

    /** 批量导入招生计划 **/
    @Override
    public JSONObject batchUpdateEnrollPlan(Map<String, Object> params) throws SQLException, BaseException, IOException {
        //注意：模板的输入必须是文本格式，不能为数字格式
        //成功的list 和失败的 list
        List<Map<String, Object>> successList = new ArrayList<>();
        List<Map<String, Object>> errorList = new ArrayList<>();
        //读取Excel
        ExportExcel exportExcel = ExportExcel.createExportExcel(params.get("url").toString(), "{SCHOOLCODE:学校代码,TBBS:托班班级数量,TBRS:托班招生人数计划,XBBS:小班班级数量,XBRS:小班招生人数计划}");
        List<Map<String, Object>> mapList = exportExcel.readExcel();
        //mapList副本
        List<Map<String, Object>> newMap = CollectionUtils.cloneDataList(mapList);
        int j = 0;
        for (Map<String, Object> data : mapList){
            String tbrs = data.get("TBRS") == null ? "" : data.get("TBRS").toString();
            //判断tbrs是否为空
            if (tbrs == null || tbrs.trim().equals("")) {
                newMap.get(j).put("remark", "托班招生人数不能为空且只能为数字");
                errorList.add(newMap.get(j));
                j++;
                continue;
            }
            String tbbs = data.get("TBBS") == null ? "" : data.get("TBBS").toString();
            //判断tbbs是否为空
            if (tbbs == null || tbbs.trim().equals("")) {
                newMap.get(j).put("remark", "托班班数不能为空且只能为数字");
                errorList.add(newMap.get(j));
                j++;
                continue;
            }
            //判断tbbs是否超过两位数
            int tbbslen = tbbs.length();
            if(tbbslen>2){
                newMap.get(j).put("remark", "托班班数不能超过99");
                errorList.add(newMap.get(j));
                j++;
                continue;
            }
            String xbrs = data.get("XBRS") == null ? "" : data.get("XBRS").toString();
            //判断xbrs是否为空
            if (xbrs == null || xbrs.trim().equals("")) {
                newMap.get(j).put("remark", "小班招生人数不能为空且只能为数字");
                errorList.add(newMap.get(j));
                j++;
                continue;
            }
            String xbbs = data.get("XBBS") == null ? "" : data.get("XBBS").toString();
            //判断xbbs是否为空
            if (xbbs == null || xbbs.trim().equals("")) {
                newMap.get(j).put("remark", "小班班数不能为空且只能为数字");
                errorList.add(newMap.get(j));
                j++;
                continue;
            }
            //判断xbbs是否超过两位数
            int xbbslen = xbbs.length();
            if(xbbslen>2){
                newMap.get(j).put("remark", "小班班数不能超过99");
                errorList.add(newMap.get(j));
                j++;
                continue;
            }
            //验证填入的学校是否正确
            QueryParams queryParams = QueryParams.createQueryParams("t_schoolinfo");
            queryParams.addQueryParams(Parameter.createParameter("deleted", "0"));
            queryParams.addQueryParams(Parameter.createParameter("schoolcode", data.get("SCHOOLCODE")));
            queryParams.printSelf();
            List<Map<String, Object>>  checkSchoolExist = baseDaoComponent.selectDataByParams(queryParams);
            if (checkSchoolExist.isEmpty()) {
                newMap.get(j).put("remark", "不存在该学校，无法修改");
                errorList.add(newMap.get(j));
                j++;
                continue;
            }
            //修改招生计划
            UpdateParams updateParams = UpdateParams.createUpdateParams("t_schoolinfo");
            updateParams.addWhereParameter(Parameter.createParameter("schoolcode", data.get("SCHOOLCODE")));
            updateParams.addParams(Parameter.createParameter("tbrs", tbrs),
                    Parameter.createParameter("tbbs", tbbs),
                    Parameter.createParameter("xbrs", xbrs),
                    Parameter.createParameter("xbbs", xbbs),
                    Parameter.createParameter("UPDATEUSERID", params.get("adminid")),
                    Parameter.createParameter("UPDATETIME", Format.getDateTime()));
            try {
                updateParams.printSelf();
                int i = baseDaoComponent.updateDataByParams(updateParams);
                System.out.print(i);
            } catch (Exception e) {
                newMap.get(j).put("remark", "填写数据错误:" + e.getMessage());
                errorList.add(newMap.get(j));
                j++;
                continue;
            }
            successList.add(newMap.get(j));
            j++;
        }
        //将错误信息写入excel中
        String url1 = writeMessageToExcel(successList, "{SCHOOLCODE:学校代码,TBBS:托班班级数量,TBRS:托班招生人数计划,XBBS:小班班级数量,XBRS:小班招生人数计划}","招录管理-招生计划管理-上传成功列表");
        String url2 = writeMessageToExcel(errorList, "{SCHOOLCODE:学校代码,TBBS:托班班级数量,TBRS:托班招生人数计划,XBBS:小班班级数量,XBRS:小班招生人数计划,REMARK:错误信息}","招录管理-招生计划管理-上传失败列表");
        int sum = successList.size() + errorList.size();
        Map<String, Object> result = new DataMap<>();
        result.put("url1", url1);
        result.put("url2", url2);
        JSONObject rs = ResponseUtils.createSuccessResponseBody("成功分入:" + successList.size() + "条,失败分入:" + errorList.size() + "条,总共处理:" + sum + "条", result);
   //     rs.put("url1", url1);
   //     rs.put("url2", url2);
        return rs;
    }

    /** 单个修改招生计划 **/
    @Override
    public JSONObject modifyEnrollPlan(Map<String, Object> params) throws SQLException, BaseException {
        //五个参数，schoolcode,tbrs,tbbs,xbrs,xbbs
        LoginInfo loginInfo = (LoginInfo) params.remove("userinfo");
        String tbrs = params.get("tbrs").toString();
        String tbbs = params.get("tbbs").toString();
        String xbrs = params.get("xbrs").toString();
        String xbbs = params.get("xbbs").toString();
        if (BaseChecks.hasEmptyStr(tbrs)) throw new BaseException("托班人数不能为空");
        if (BaseChecks.hasEmptyStr(tbbs)) throw new BaseException("托班班数不能为空");
        if (BaseChecks.hasEmptyStr(xbrs)) throw new BaseException("小班人数不能为空");
        if (BaseChecks.hasEmptyStr(xbbs)) throw new BaseException("小班班数不能为空");
        UpdateParams updateParams = UpdateParams.createUpdateParams("t_schoolinfo");
        updateParams.addWhereParameter(Parameter.createParameter("id",params.get("id")));
        updateParams.addParam(Parameter.createParameter("TBRS",params.get("tbrs")));
        updateParams.addParam(Parameter.createParameter("TBBS",params.get("tbbs")));
        updateParams.addParam(Parameter.createParameter("XBRS",params.get("xbrs")));
        updateParams.addParam(Parameter.createParameter("XBBS",params.get("xbbs")));
        updateParams.addParam(Parameter.createParameter("updatetime", Format.getDateTime()));
        updateParams.addParam(Parameter.createParameter("updateuserid",params.get("adminid")));
        try {
            updateParams.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        int i = baseDaoComponent.updateDataByParams(updateParams);
        if(i==0)throw new DataNotFoundException();
        return ResponseUtils.createSuccessResponseBody("修改成功");
    }

    /** 录取信息下载设置 **/
    public JSONObject updateEnrollOpenTime(Map<String, Object> params) throws SQLException, DataNotFoundException, ParseException, EnrollOpenTimeException {
        //如果是按开关不按时间来控制，则一开始按区县code和opentype=1来查找，现在按时间控制
        LoginInfo loginInfo = (LoginInfo) params.get("userinfo");
        String districtcode = loginInfo.getDistrictcode();
        String openType = (String)params.get("opentype");
        Date startTime = null,endTime = null;
        //参数{districtcode:区县code,starttime:开启时间,endtime:结束时间，opentype:打开类型,schoolgrade:学校等级}
        if (!"4".equals(openType)){
            startTime = DateUtils.parseDate((String) params.get("starttime"), new String[]{"yyyyMMdd", "yyyy-MM-dd"});
            endTime = DateUtils.parseDate((String) params.get("endtime"), new String[]{"yyyyMMdd", "yyyy-MM-dd"});
            //校验开始时间是否早于结束时间
            int compare = startTime.compareTo(endTime);
            if(compare!=-1){     //不早于
                throw new EnrollOpenTimeException();
            }
        }

        //先查询记录是否存在，存在的新增，不存在的更新
        QueryParams queryParams = QueryParams.createQueryParams("T_OPEN_CONFIGTIME");
        if("4".equals(openType)){     //设置区县报名个数
            queryParams.addQueryParams(Parameter.createParameter("districtcode",params.get("districtcode")),Parameter.createParameter("opentype",params.get("opentype")));
        }else{     //设置时间
            queryParams.addQueryParams(Parameter.createParameter("districtcode",districtcode),Parameter.createParameter("opentype",params.get("opentype")),Parameter.createParameter("schoolgrade",params.get("schoolgrade")),Parameter.createParameter("xxbb", params.get("xxbb")));
        }
        try {
            queryParams.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        queryParams.printSelf();
        List<Map<String, Object>> result = baseDaoComponent.selectDataByParams(queryParams);
        if (result.isEmpty()){       //为空要新增
            if ("4".equals(openType)) {    //新增区县报名个数
                String uuid = UUID.randomUUID().toString();
                InsertParams insertParams = InsertParams.createInsertParams("t_open_configtime","ID","STARTTIME","ENDTIME","DISTRICTCODE","ADDUSERID","ADDTIME","UPDATEUSERID","UPDATETIME","VERIFICATION","REMARK","OPENTYPE","SCHOOLGRADE","XXBB");
                insertParams.setValues(uuid,null,null,params.get("districtcode"),params.get("adminid"),Format.getDateTime(),null,null,params.get("verification"),null,"4",null,"1");
                try {
                    insertParams.setDbLog(loginInfo);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                baseDaoComponent.insertDataByParams(insertParams);
            }else{     //新增时间
                String uuid = UUID.randomUUID().toString();
                InsertParams insertParams = InsertParams.createInsertParams("t_open_configtime","ID","STARTTIME","ENDTIME","DISTRICTCODE","ADDUSERID","ADDTIME","UPDATEUSERID","UPDATETIME","VERIFICATION","REMARK","OPENTYPE","SCHOOLGRADE","XXBB");
                insertParams.setValues(uuid,DateFormatUtils.format(startTime, "yyyyMMdd"),DateFormatUtils.format(endTime, "yyyyMMdd"),districtcode,params.get("adminid"),Format.getDateTime(),null,null,"1",null,openType,params.get("schoolgrade"),params.get("xxbb"));
                try {
                    insertParams.setDbLog(loginInfo);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                baseDaoComponent.insertDataByParams(insertParams);
            }
            return ResponseUtils.createSuccessResponseBody("修改成功");
        }else {
            //更新设置
            UpdateParams updateParams = UpdateParams.createUpdateParams("T_OPEN_CONFIGTIME");

            if ("4".equals(openType)) {    //配置区县报名个数
                updateParams.addWhereParameters(Parameter.createParameter("districtcode", params.get("districtcode")),
                        Parameter.createParameter("opentype", params.get("opentype")));
                updateParams.addParams(Parameter.createParameter("verification", params.get("verification")),Parameter.createParameter("updateuserid", params.get("adminid")),Parameter.createParameter("updatetime", Format.getDateTime()));
            } else {    //修改时间
                updateParams.addWhereParameters(Parameter.createParameter("districtcode", districtcode),
                        Parameter.createParameter("opentype", params.get("opentype")), Parameter.createParameter("schoolgrade", params.get("schoolgrade")),Parameter.createParameter("xxbb", params.get("xxbb")));
                updateParams.addParams(Parameter.createParameter("starttime", DateFormatUtils.format(startTime, "yyyyMMdd")),
                        Parameter.createParameter("endTime", DateFormatUtils.format(endTime, "yyyyMMdd")),Parameter.createParameter("verification", "1"),Parameter.createParameter("updateuserid", params.get("adminid")),Parameter.createParameter("updatetime", Format.getDateTime()));
            }
            updateParams.printSelf();
            try {
                updateParams.setDbLog(loginInfo);
            } catch (Exception e) {
                e.printStackTrace();
            }
            int i = baseDaoComponent.updateDataByParams(updateParams);
            if (i == 0) throw new DataNotFoundException();
            return ResponseUtils.createSuccessResponseBody("修改成功");
        }
   /*     QueryParams queryParams = QueryParams.createQueryParams("T_OPEN_CONFIGTIME");
        queryParams.addQueryParams(Parameter.createParameter("districtcode",params.get("districtcode")),Parameter.createParameter("opentype","1"));
        try {
            queryParams.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        queryParams.printSelf();
        List<Map<String, Object>> result = baseDaoComponent.selectDataByParams(queryParams);
        if(result.isEmpty()){     //为空要新增
            String uuid = UUID.randomUUID().toString();
            InsertParams insertParams = InsertParams.createInsertParams("t_open_configtime","ID","STARTTIME","ENDTIME","DISTRICTCODE","ADDUSERID","ADDTIME","UPDATEUSERID","UPDATETIME","VERIFICATION","REMARK","OPENTYPE");
            insertParams.setValues(uuid,null,null,params.get("districtcode"),params.get("adminid"),Format.getDateTime(),null,null,params.get("verification"),null,"1");
            try {
                insertParams.setDbLog(loginInfo);
            } catch (Exception e) {
                e.printStackTrace();
            }
            baseDaoComponent.insertDataByParams(insertParams);
            JSONObject rs = ResponseUtils.createSuccessResponseBody("录取时间修改成功");
            return rs;
        }else{          //不为空就修改
            UpdateParams updateParams = UpdateParams.createUpdateParams("t_open_configtime");
            updateParams.addWhereParameters(Parameter.createParameter("districtcode",params.get("districtcode")),Parameter.createParameter("opentype","1"));
            updateParams.addParams(createParameter("verification",params.get("verification")),
                    createParameter("updateuserid",params.get("adminid")),
                    createParameter("updatetime",Format.getDateTime()));
            try {
                updateParams.setDbLog(loginInfo);
            } catch (Exception e) {
                e.printStackTrace();
            }
            int i = baseDaoComponent.updateDataByParams(updateParams);
            if(i==0)throw new DataNotFoundException();
            JSONObject rs = ResponseUtils.createSuccessResponseBody("录取时间修改成功");
            return rs;
        }*/
    }

    /** 录取设置预加载 **/
    public JSONObject queryEnrollOpenTime(Map<String, Object> params) throws SQLException, DataNotFoundException{
       //查询1个opentype中所有的schoolgrade，有六个框
        //4.6 在预加载和修改的时候会多一个民办公办的字段，要注意添加
        LoginInfo loginInfo = (LoginInfo) params.get("userinfo");
        QueryParams queryTime = QueryParams.createQueryParams("T_OPEN_CONFIGTIME");
        String opentype = params.get("opentype").toString();
        if("4".equals(opentype)){
            queryTime.addQueryParams(Parameter.createParameter("opentype", params.get("opentype")));
        }else{
            queryTime.addQueryParams(Parameter.createParameter("districtcode", params.get("districtcode")),Parameter.createParameter("opentype", params.get("opentype")));
        }
        try {
            queryTime.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
  //      Map<String, Object> timeMap = baseDaoComponent.selectOneDataByParams(queryTime);
  //      return ResponseUtils.createSuccessResponseBody("录取设置查询成功", timeMap);
        List<Map<String, Object>> result = baseDaoComponent.selectDataByParams(queryTime);
        JSONObject rs = ResponseUtils.createSuccessResponseBody("录取设置查询成功", result);
        return rs;
   /*     QueryParams queryParams = QueryParams.createQueryParams("t_baomingluquinfo");
        queryParams.addQueryParams(Parameter.createParameter("districtcode",params.get("districtcode")),Parameter.createParameter("LQZT", EOperators.包含,"0","2"));
        queryParams.sethasColums(true);
        queryParams.addColumns("count(id) as D1");
        try {
            queryParams.setDbLog(loginInfo);
        } catch (Exception e) {
            e.printStackTrace();
        }
        queryParams.printSelf();
        List<Map<String, Object>> result = baseDaoComponent.selectDataByParams(queryParams);
        JSONObject rs = ResponseUtils.createSuccessResponseBody("done", result);
        return rs;*/
    }

    /**
     * 写入错误和成功信息表格
     **/
    public String writeMessageToExcel(List<Map<String, Object>> list, String excelHead, String title) throws IOException {
        String absPath = "";
        String virPath = "";
        try {
            FileInfoPo fileInfoPo = FileIOComponent.createTempFilePathByType(FileIOComponent.EXPORT_TYPE,"xlsx",title);
            absPath = fileInfoPo.getFileAbsPath();
            virPath = fileInfoPo.getFileVirPath();
            ExportExcel exportExcel = ExportExcel.createExportExcel(absPath, excelHead);
            exportExcel.setCurrentData(list);
            exportExcel.writeCurrentData();
        } catch (FileTypeNotFoundException e) {
            e.printStackTrace();
        }
        return virPath;
    }
}
